﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
    public static class ViewsDataAccess {

        public static IEnumerable<HistoryBill> SearchHistoryBillByHouse(int? houseId,bool? isComplete,DateTime? stime,DateTime? etime) {
            var sql =
                "select * from dbo.HistoryBills3 where 1=1 "
                ;
            var paramList = new List<SqlParameter>();
            if (houseId.HasValue) {
                sql += " and HouseID = @houseId ";
                paramList.Add(new SqlParameter("houseId", houseId.Value));
            }
            if (isComplete != null) {
                sql += " and IsCompletePaid = @ispaid ";
                paramList.Add(new SqlParameter("ispaid", isComplete.Value?1:0));
            }
            if (stime.HasValue) {
                sql += " and PaidDate >= @stime ";
                paramList.Add(new SqlParameter("stime", stime.Value));
            }
            if (etime.HasValue) {
                sql += " and PaidDate <= @etime ";
                paramList.Add(new SqlParameter("etime", etime.Value));
            }

            var list = new List<HistoryBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray( ))) {
                while (iReader.Read( )) {
                    var bill = new HistoryBill {
                        BillID =  iReader["PropertyManagementFeeBillID"] is DBNull ? default(Int32) : (Int32)iReader["PropertyManagementFeeBillID"] ,
                        ApartmentName =
                            iReader["ApartmentName"] is DBNull ? default(String) : (String) iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String) iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String) iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType) iReader["BillType"],
                        CallableMoney =
                            iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal) iReader["CallableMoney"],
                        BillEffectiveDate =
                            iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime) iReader["EffectiveDate"],
                        ExpireDate =
                            iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime) iReader["ExpireDate"],


                        IsCompletedPaid =
                            iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean) iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime) iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal) iReader["PaidMoney"],

                        LastUpdate =
                            iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime) iReader["LastUpdate"],
                        LastActionUserName =
                            iReader["UserName"] is DBNull ? default(String) : (String) iReader["UserName"]
                    };
                    bill.UnitPrice =  FeeUnitPriceManageDataAccess.GetUnitByBillType(bill.BillType).UnitPrice;
                    var currentValue = iReader["CurrentValue"] is DBNull ? default(Int32) : (Int32) iReader["CurrentValue"];

                    var lastValue = iReader["LastValue"] is DBNull ? default(Int32) : (Int32) iReader["LastValue"];
                    if (currentValue != 0) {
                        bill.FeeNumber = currentValue - lastValue;
                    }
                    else {
                        bill.FeeNumber = iReader["SpaceArea"] is DBNull ? default(Int32) : (Int32) iReader["SpaceArea"];
                    }
                    list.Add(bill);
                };
            }
            return list;
        } 

        public static IEnumerable<HistoryBill> SearchHistoryBillByHouse(int? houseId,bool? isComplete, DateTime? month) {
            var sql =
                "select * from dbo.HistoryBills3 where 1=1  "
                ;
            var paramList = new List<SqlParameter>( );
            if (houseId.HasValue) {
                sql += " and HouseID = @houseId ";
                paramList.Add(new SqlParameter("houseId", houseId.Value));
            }
            if (isComplete != null) {
                sql += " and IsCompletePaid = @ispaid ";
                paramList.Add(new SqlParameter("ispaid", isComplete.Value ? 1 : 0));
            }
            if (month.HasValue) {
                sql += " and BillMonth = @month ";
                paramList.Add(new SqlParameter("month", month.Value));
            }
        
            var list = new List<HistoryBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray( ))) {
                while (iReader.Read( )) {
                    var bill = new HistoryBill {
                        BillID = iReader["PropertyManagementFeeBillID"] is DBNull ? default(Int32) : (Int32)iReader["PropertyManagementFeeBillID"],
                        ApartmentName =
                            iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"],
                        CallableMoney =
                            iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"],
                        BillEffectiveDate =
                            iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"],
                        ExpireDate =
                            iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"],


                        IsCompletedPaid =
                            iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"],

                        LastUpdate =
                            iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName =
                            iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    };
                    bill.UnitPrice = FeeUnitPriceManageDataAccess.GetUnitByBillType(bill.BillType).UnitPrice;
                    var currentValue = iReader["CurrentValue"] is DBNull ? default(Int32) : (Int32)iReader["CurrentValue"];

                    var lastValue = iReader["LastValue"] is DBNull ? default(Int32) : (Int32)iReader["LastValue"];
                    if (currentValue != 0) {
                        bill.FeeNumber = currentValue - lastValue;
                    } else {
                        bill.FeeNumber = iReader["SpaceArea"] is DBNull ? default(Int32) : (Int32)iReader["SpaceArea"];
                    }
                    list.Add(bill);
                };
            }
            return list;
        } 

        public static IEnumerable<HistoryInstrumentBill> SearchHistroyInstrumentBills(BillType type, DateTime sTime, DateTime eTime) {
            var sql =
                "select * from dbo.HistoryBills where BillType =@billtype and EffectiveDate>=@strartime and EffectiveDate <= @endtime "
                ;
            var paramList = new List<SqlParameter> {
                new SqlParameter("strartime", sTime),
                new SqlParameter("endtime", eTime),
                new SqlParameter("billtype", (int)type)
            };


            var list = new List<HistoryInstrumentBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,paramList.ToArray())) {
                while (iReader.Read( )) {
                    list.Add(new HistoryInstrumentBill {
                        ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"],
                        CallableMoney = iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"],
                        BillEffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"],
                        ExpireDate = iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"],
                        CurrentDate = iReader["CurrentDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CurrentDate"],
                        CurrentValue = iReader["CurrentValue"] is DBNull ? default(Int32) : (Int32)iReader["CurrentValue"],
                        LastDate = iReader["LastDate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastDate"],
                        LastValue = iReader["LastValue"] is DBNull ? default(Int32) : (Int32)iReader["LastValue"],
                        InstrumentType = iReader["InstrumentType"] is DBNull ? default(InstrumentType) : (InstrumentType)iReader["InstrumentType"],

                        IsCompletedPaid = iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"],

                        LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    });
                };
            }
            return list;
        }


        public static IEnumerable<HistoryInstrumentBill> SearchHistroyInstrumentBills(BillType type, DateTime month) {
            var sql =
                "select * from dbo.HistoryBills where BillType =@billtype and BillMonth>=@month "
                ;
            var paramList = new List<SqlParameter> {
                new SqlParameter("month", month),
              
                new SqlParameter("billtype", (int)type)
            };


            var list = new List<HistoryInstrumentBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray( ))) {
                while (iReader.Read( )) {
                    list.Add(new HistoryInstrumentBill {
                        ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"],
                        CallableMoney = iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"],
                        BillEffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"],
                        ExpireDate = iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"],
                        CurrentDate = iReader["CurrentDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CurrentDate"],
                        CurrentValue = iReader["CurrentValue"] is DBNull ? default(Int32) : (Int32)iReader["CurrentValue"],
                        LastDate = iReader["LastDate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastDate"],
                        LastValue = iReader["LastValue"] is DBNull ? default(Int32) : (Int32)iReader["LastValue"],
                        InstrumentType = iReader["InstrumentType"] is DBNull ? default(InstrumentType) : (InstrumentType)iReader["InstrumentType"],

                        IsCompletedPaid = iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"],

                        LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    });
                };
            }
            return list;
        }


        public static IEnumerable<HistoryManagementBill> SearchHistoryManagementBills(BillType type, DateTime sTime, DateTime eTime) {
            var sql =
                "select * from dbo.HistoryBills2 where BillType =@billtype and EffectiveDate>=@strartime and EffectiveDate <= @endtime "
                ;
            var paramList = new List<SqlParameter> {
                new SqlParameter("strartime", sTime),
                new SqlParameter("endtime", eTime),
                new SqlParameter("billtype", (int)type)
            };
            var list = new List<HistoryManagementBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray())) {
                while (iReader.Read( )) {
                    list.Add(new HistoryManagementBill {
                        ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"],
                        CallableMoney = iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"],
                        BillEffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"],
                        ExpireDate = iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"],
                        SpaceArea = iReader["SpaceArea"] is DBNull ? default(Int32) : (Int32)iReader["SpaceArea"],

                        IsCompletedPaid = iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"],

                        LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    });
                };
            }
            return list;
        }

        public static IEnumerable<HistoryManagementBill> SearchHistoryManagementBills(BillType type, DateTime month) {
            var sql =
                "select * from dbo.HistoryBills2 where BillType =@billtype and  BillMonth <= @month "
                ;
            var paramList = new List<SqlParameter> {
                new SqlParameter("month", month),
                new SqlParameter("billtype", (int)type)
            };
            var list = new List<HistoryManagementBill>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray( ))) {
                while (iReader.Read( )) {
                    list.Add(new HistoryManagementBill {
                        ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"],
                        CallableMoney = iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"],
                        BillEffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"],
                        ExpireDate = iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"],
                        SpaceArea = iReader["SpaceArea"] is DBNull ? default(Int32) : (Int32)iReader["SpaceArea"],

                        IsCompletedPaid = iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"],
                        PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"],
                        PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"],

                        LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    });
                };
            }
            return list;
        }

        public static IEnumerable<MaintainView> SearchHistoryMaintains(bool? status, DateTime sTime, DateTime eTime) {
            var sql =
                "select * from dbo.HistroyMaintainsView where  ApplyDate>=@stime and ApplyDate <= @etime";
            var paramList = new List<SqlParameter> {
                new SqlParameter("stime", sTime),
                new SqlParameter("etime", eTime),
               
            };
            
            if (status != null) {
                sql += " and Status = @status ";
                paramList.Add(new SqlParameter("status", status));
            }
         
            
            var list = new List<MaintainView>( );
            using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList.ToArray( ))) {
                while (iReader.Read( )) {
                    list.Add(new MaintainView {
                        MaintainID = iReader["MaintainID"] is DBNull ? default(Int32) : (Int32)iReader["MaintainID"],
                        ApartmentName = iReader["ApartmentName"] is DBNull ? default(String) : (String)iReader["ApartmentName"],
                        HouseCode = iReader["HouseCode"] is DBNull ? default(String) : (String)iReader["HouseCode"],
                        OwnerName = iReader["OwnerName"] is DBNull ? default(String) : (String)iReader["OwnerName"],
                        
                        MaintainCode = iReader["MaintainCode"] is DBNull ? default(String) : (String)iReader["MaintainCode"] ,
							ApplyDate = iReader["ApplyDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ApplyDate"] ,
							MaintainTopic = iReader["MaintainTopic"] is DBNull ? default(String) : (String)iReader["MaintainTopic"] ,
							MaintainType = iReader["MaintainType"] is DBNull ? default(MaintainType) : (MaintainType)iReader["MaintainType"] ,
							Remark = iReader["Remark"] is DBNull ? default(String) : (String)iReader["Remark"] ,
							RepaireDate = iReader["RepaireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["RepaireDate"] ,
							RepaireWorker = iReader["RepaireWorker"] is DBNull ? default(String) : (String)iReader["RepaireWorker"] ,
							Status = iReader["Status"] is DBNull ? default(Boolean) : (Boolean)iReader["Status"],

                        MaintainFee = iReader["MaintainFee"] is DBNull ? default(Decimal) : (Decimal)iReader["MaintainFee"],
                        
                     

                        LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"],
                        LastActionUserName = iReader["UserName"] is DBNull ? default(String) : (String)iReader["UserName"]
                    });
                };
            }
            return list;
        } 
    }
}
